﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTSASData : BaseFunction
    {

        public string flag = "";
        public JLTSASData()
            : base()
        {

        }
        public JLTSASData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetSAS(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "SAS";
            string FROMDATE = dwABPOS["FROMDATE"].ToString().Trim();
            string ENDDATE = dwABPOS["ENDDATE"].ToString().Trim();
            string STAFF = dwABPOS["STAFF"].ToString().Trim();
            string ARTICLE = dwABPOS["ARTICLE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT TRIM(X.WBEWART)||'-'||TRIM(X.WBEWFARBE) AS ART, X.VKKSACHBE, "
                    + " X.NAME,X.INQTY,X.CUTQTY,X.SHIPQTY,decode(X.INQTY,0,0,X.INQTY-X.CUTQTY-X.SHIPQTY) AS STOCKQTY "
                    + " FROM ( SELECT A.WBEWART,A.WBEWFARBE,K.VKKSACHBE,TRIM(H.HRNNAME) AS NAME, "
                    + " SUM(DECODE(A.WBEWLB,'CL',DECODE(A.WBEWBS,'10',A.WBEWMTR*0.001,'13',A.WBEWMTR*0.001,0),0)) AS INQTY, "
                    + " SUM(DECODE(A.WBEWLB,'LC',DECODE(A.WBEWBS,'21',A.WBEWMTR*0.001,'20',-A.WBEWMTR*0.001,0),0)) AS CUTQTY, "
                    + " SUM(DECODE(A.WBEWAUFTYP,' ',0,DECODE(A.WBEWBS,'61',A.WBEWMTR*0.001,'62',-A.WBEWMTR*0.001,0))) AS SHIPQTY, "
                    + " SUM(DECODE(A.WBEWLB,'CC',DECODE(A.WBEWBS,'21',A.WBEWMTR*0.001,'20',-A.WBEWMTR*0.001,0),0)) AS STOCKQTY, "
                    + " SUM(DECODE(A.WBEWLB,'CL',DECODE(A.WBEWBS,'42',A.WBEWMTR*0.001,'43',-A.WBEWMTR*0.001,0),0)) AS ADJQTY "
                    + " FROM WBBEW A,VKKOPF K,HRSTAMM H "
                    + " WHERE A.WBEWBUCHDAT >='" + FROMDATE + "' AND A.WBEWBUCHDAT <='" + ENDDATE + "' "
                    + " AND A.WBEWBS IN ('10','13','20','21','42','43','61','62') "
                    + " AND A.WBEWART LIKE 'S%' AND A.WBEWWAKZ = 'F' "
                    + " AND K.VKKSA<>'AJ' AND K.VKKMANDANT = A.WBEWMANDANT "
                    + " AND K.VKKAUFKRS||'-'||SUBSTR(K.VKKAUFLFD,3,4) = TRIM(A.WBEWART) "
                    + " AND K.VKKMANDANT = H.HRMANDANT AND K.VKKSACHBE = H.HRBENID "
                    + " AND (TRIM(K.VKKSACHBE) = '" + STAFF + "'  OR '" + STAFF + "'='AAA' ) "
                    + " AND  TRIM(A.WBEWART)||'-'||TRIM(A.WBEWFARBE) like '" + ARTICLE + "%' "
                    + " GROUP BY WBEWART,WBEWFARBE,K.VKKSACHBE,H.HRNNAME "
                    + " ) X WHERE X.INQTY<>0 OR X.CUTQTY<>0 OR X.SHIPQTY<>0 " 

                    ;

                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["NAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["NAME"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        
        public DataSet BuildJLTSASData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
               
                setResult = GetSAS(dwUI);               
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}